﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Model.TrainModelHelper;

namespace DAL.TrainDALhelp
{
    public class ItembankDAL
    {
        private static string ConStr = ConfigurationManager.ConnectionStrings["ConStr"].ToString();
        private static SqlConnection conn = new SqlConnection(ConStr);
        //显示试题题目

        public int ItemCount()
        {
            conn.Open();
            SqlCommand comd = new SqlCommand("select COUNT(*) from  ItembankTable", conn);
            var I = comd.ExecuteScalar();
            conn.Close();
            return Convert.ToInt32( I);

        }

        public List<ItembankModel> ItembankList()
        {
            conn.Open();
            SqlCommand comd = new SqlCommand("select * from  ItembankTable", conn);
            SqlDataReader read = comd.ExecuteReader();
            List<ItembankModel> lis = new List<ItembankModel>();
            while (read.Read())
            {
                ItembankModel str = new ItembankModel();

                str.ItemId = Convert.ToInt32(read["ItemId"]);
                //题号
                str.ItemProId = Convert.ToInt32(read["ItemProId"]);
                str.ItemName = read["ItemName"].ToString();

                ////题号
                //str.ItemPId = Convert.ToInt32(read["ItemProId"]);
                ////判断答案！
                //str.ResulProId = Convert.ToInt32(read["ResulProId"]);
                ////答案
                //str.ResultName = read["ResultName"].ToString();
                ////答案Id
                //str.ResultId = Convert.ToInt32(read["ResultId"]);

                lis.Add(str);
            }
            read.Close();
            conn.Close();
            return lis;
        }
        //导入试题
        public int ItemBankAdd(string path)
        {
            OleDbHelper a = new OleDbHelper(path);
            DataSet DS = OleDbHelper.GetDataSet("Select *From [Sheet1$]");
            DataTable DT = DS.Tables[0];
            string sql = "";
            for (int i = 0; i < DT.Rows.Count; i++)
            {
                if (DT.Rows[i]["题目"].ToString() != "")
                {
                    string Name = DT.Rows[i]["题目"].ToString();
                    string ItemPro = DT.Rows[i]["题型"].ToString();
                    int ItemProId = Convert.ToInt32(ItemPro);
                    sql += "insert into ItembankTable  values ('" + Name + "'," + ItemProId + ");";
                }
                else
                {
                    continue;

                }
            }

            var RequstSQL = "";
            for (int i = 0; i < DT.Rows.Count; i++)
            {
                if (DT.Rows[i]["答案"].ToString() != "")
                {
                    string ItemPro = DT.Rows[i]["题型"].ToString();
                    int ItemProId = Convert.ToInt32(ItemPro);

                    string RequstName = DT.Rows[i]["答案"].ToString();

                    string RequstPro = DT.Rows[i]["是否正确"].ToString();
                    int RequstProId = 0;
                    if (RequstPro != "")
                    {
                        RequstProId = Convert.ToInt32(RequstPro);

                    }
                    else
                    {
                        RequstProId = 0;
                    }

                    RequstSQL += "insert into ResultTable  values ('" + RequstName + "'," + ItemProId + "," + RequstProId + ");";
                }
                else
                {
                    continue;

                }
            }


            conn.Open();
            SqlCommand comds = new SqlCommand(RequstSQL, conn);
            comds.ExecuteNonQuery();
            SqlCommand comd = new SqlCommand(sql, conn);

            int I = comd.ExecuteNonQuery();
            conn.Close();
            return I;
        }
        //删除试题
        public int DeleteItemBank(string ID)
        {

            conn.Open();
            SqlCommand comd = new SqlCommand("DELETE FROM ItembankTable WHERE  ItemId  in (" + ID + ")", conn);
            SqlCommand comds = new SqlCommand("DELETE FROM ResultTable  WHERE  ItemId  in (" + ID + ")", conn);
            comds.ExecuteNonQuery();
            var reqult = comd.ExecuteNonQuery();




            conn.Close();
            return reqult;


        }

    }
}
